# Create your views here.
from django.http import HttpResponse
from django.template import Context, loader, Template
from django.shortcuts import render_to_response
from django.utils import simplejson as json

import pyodbc

odbc_conn_str = 'DRIVER={SQL Server};SERVER=interimsys;DATABASE=SMS;UID=SMS_Reader;PWD=SMS_Reader'

def index(self, request):
    return HttpResponse("Hello world!")

def getRelList(request):
    exe_statement = 'select MainRelease_ID, MainReleaseName from MainRelease where InMaintain = 1 and End_Date > 2009-01-01'
    conn = pyodbc.connect(odbc_conn_str)
    cursor = conn.cursor()
    cursor.execute(exe_statement)

    patches = []
    for row in cursor:
        patches.append({
            'RelName' : row.MainReleaseName,
            'RelId' : row.MainRelease_ID,
            })
    return render_to_response('release_list.html', {'rel' : patches})

def getRelListtoJSON(request):
    exe_statement = "select MainRelease_ID, MainReleaseName from MainRelease where InMaintain = 1  and End_Date > 2009-01-01"
    conn = pyodbc.connect(odbc_conn_str)
    cursor = conn.cursor()
    cursor.execute(exe_statement)

    patches = []
    for row in cursor:
        patches.append({
            'RelName' : row.MainReleaseName,
            'RelId' : row.MainRelease_ID,
            })
def getPatchByRel(request, rel_id):
    exe_statement = "select r.MainRelease_ID, r.MainReleaseName, " \
        " p.PL_ID, p.PL_Name, s.Status_Name, p.DevStart, p.DevClose, p.GUIFreeze, p.RTC, p.IsCompanyDBUpgrade "\
        " from vw_PatchStatus as s, Patch as p, MainRelease as r " \
        " where s.Status_ID = p.Status_ID " \
        " and p.MainRelease_ID = r.MainRelease_ID " \
        " and r.MainRelease_ID = %s " \
        " order by r.MainReleaseName, p.PL_ID" % rel_id
    conn = pyodbc.connect(odbc_conn_str)
    cursor = conn.cursor()
    cursor.execute(exe_statement)

    patches = []
    for row in cursor:
        patches.append({
            'MainReleaseId' : row.MainRelease_ID,
            'MainReleaseName' : row.MainReleaseName,
            'PatchName' : row.PL_Name,
            'PatchId' : row.PL_ID,
            'PatchStatus' : row.Status_Name,
            'DevStartDate' : row.DevStart,
            'DevCloseDate': row.DevClose,
            'UpgradePatch' : row.IsCompanyDBUpgrade,
            })
    return render_to_response('patch_list.html', {'patches' : patches})

def getPatchById(self, patch_id):
    exe_statement = "select r.MainRelease_ID, r.MainReleaseName, " \
        " p.PL_ID, p.PL_Name, s.Status_Name, p.DevStart, p.DevClose, p.GUIFreeze, p.RTC, p.IsCompanyDBUpgrade "\
        " from vw_PatchStatus as s, Patch as p, MainRelease as r " \
        " where s.Status_ID = p.Status_ID " \
        " and p.MainRelease_ID = r.MainRelease_ID " \
        " and p.PL_ID = %s " \
        " order by r.MainReleaseName, p.PL_ID" % patch_id
    conn = pyodbc.connect(odbc_conn_str)
    cursor = conn.cursor()
    row = cursor.execute(exe_statement).fetchone()

    patch = {
        'MainReleaseId' : row.MainRelease_ID,
        'MainReleaseName' : row.MainReleaseName,
        'PatchName' : row.PL_Name,
        'PatchId' : row.PL_ID,
        'PatchStatus' : row.Status_Name,
        }

    # Patch tasks summary
    exe_statement = " select S.Status_Name, T.Status_ID, count(T.Task_ID) as Number " \
                    " from vw_TaskList as T, vw_TaskStatus as S " \
                    " where T.PL_ID = %s " \
                    " and T.Status_ID = S.Status_ID " \
                    " group by T.Status_ID, S.Status_Name " % patch_id
    cursor.execute(exe_statement)

    status = []
    for t in cursor:
        status.append({
                'StatusId' : t.Status_ID,
                'StatusName' : t.Status_Name,
                'Count' : t.Number})

    return render_to_response('patch.html', {
                                    'patch' : patch,
                                    'status' : status})

def getPatchTaskByStatus(request, patch_id, status_id):
    exe_statement = " Select I.Incident_ID,I.Report_MainRelease_ID, P.PL_ID as Report_PL_ID, P.PL_Name as Report_PL_Name, " \
                    " T.Task_ID, T.ShortText, T.Priority, T.Status_ID, T.Customer_Msg_ID, T.Internal_Msg_ID, " \
                    " M.MainReleaseName as Report_MainRelease_Name " \
                    " from vw_TaskList as T, vw_IncidentList as I, Patch as P, MainRelease as M " \
                    " where T.Incident_ID = I.Incident_ID " \
                    " and I.Report_PL_ID = P.PL_Name " \
                    " and I.Report_MainRelease_ID = M.MainRelease_ID " \
                    " and P.MainRelease_ID = I.Report_MainRelease_ID " \
                    " and T.PL_ID = %s " \
                    " and T.Status_ID = %s " \
                    " order by T.Priority " % (patch_id, status_id)
    conn = pyodbc.connect(odbc_conn_str)
    cursor = conn.cursor()
    cursor.execute(exe_statement)

    tasks = []
    for row in cursor:
        tasks.append({
            'Incident_ID' : row.Incident_ID,
            'Report_MainRelease_ID' : row.Report_MainRelease_ID,
            'Report_MainRelease_Name' : row.Report_MainRelease_Name,
            'Report_PL_ID' : row.Report_PL_ID,
            'Report_PL_Name' : row.Report_PL_Name,
            'Task_ID' : row.Task_ID,
            'ShortText' : row.ShortText,
            'Priority' : row.Priority,
            'Customer_Msg_ID': '' if row.Customer_Msg_ID is None else row.Customer_Msg_ID,
            'Internal_Msg_ID' : '' if row.Internal_Msg_ID is None else row.Internal_Msg_ID
            })
    return render_to_response('task_list.html', {'tasks' : tasks})


def getNotebyPatch(self, request):
    conn = pyodbc.connect(odbc_conn_str)
    cursor = conn.cursor()
    cursor.execute("")